package com.source.lightfowl.person.dao;

import com.source.lightfowl.person.entity.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;

@Transactional
public interface SysUserDao extends JpaRepository<Person,String>{

    @Query(
            value = "SELECT * FROM sys_user",
            nativeQuery = true
    )
    List<Person> findAll();

    @Modifying
    @Query(
            value = "update sys_user u set u.role = ?1 where u.id = ?2",
            nativeQuery = true
    )
    void setFixedFirstnameFor(String firstname, String lastname);

    @Modifying
    @Query(
            value = "update sys_user u set u.role = ?1 where u.id = ?2",
            nativeQuery = true
    )
    void setCharactFor(String role, String id);

    @Query(
            value = "SELECT * FROM sys_user WHERE loginName LIKE ?1",
            nativeQuery = true
    )
    List<Person> findByNameLike(String Name);

    @Query(
            value = "SELECT * FROM sys_user WHERE loginName=?1",
            nativeQuery = true
    )
    Person findByUsername(String loginName);

    @Query(value = "select * from sys_user where loginName = ?1 and password = ENCODE(?2,?3)",nativeQuery = true)
    Person findByUsernameAndPassword(String username, String password, String key);

    @Query(
            value = "select * from sys_user where id=?",
            nativeQuery = true
    )
    Person findUserById(String id);

    @Query(
            value = "delete from sys_user where id=?",
            nativeQuery = true
    )
    @Modifying
    void deleteByPerson_id(String id);

    @Query(
            value = "update sys_user set role=null where id=?",
            nativeQuery = true
    )
    @Modifying
    void deleteUserRole(String id);

    @Query(
            value = "SELECT bpid, bpname" +
                    " FROM `big_plate`" +
                    " WHERE bpid in" +
                        "(SELECT bpid " +
                        "from role_plate" +
                        " WHERE rid=" +
                            "(SELECT role" +
                            " FROM sys_user" +
                            " WHERE id=?1))",
            nativeQuery = true
    )
    List<Map<String, String>> findBpById(String userId);

    @Query(
            value = "SELECT ptype,pname" +
                    " FROM `plate`" +
                    " WHERE bigpid='?2'" +
                    " AND ptype in" +
                        "(SELECT ptype" +
                        " from role_plate" +
                        " WHERE bpid='?2'" +
                        " AND rid=" +
                            "(SELECT role" +
                            " FROM sys_user" +
                            " WHERE id=?1))",
            nativeQuery = true
    )
    List<Map<String, String>> findSp(String userId, String bpid);

    /* 原person
    @Modifying
    @Query(
            value = "update person u set u.rid = ?1 where u.personId = ?2",
            nativeQuery = true
    )
    void setFixedFirstnameFor(String firstname, String lastname);

    @Modifying
    @Query(
            value = "update Person u set u.rid = ?1 where u.personId = ?2",
            nativeQuery = true
    )
    void setCharactFor(String role, BigInteger id);


//    @Query(
//            value = "select person_id,email,name,phone,register_time,rid,username from person",
//            nativeQuery = true
//    )
//    List<Person> findAllUser();

    @Query(
            value = "SELECT * FROM person WHERE username LIKE ?1",
            nativeQuery = true
    )
    List<Person> findByNameLike(String Name);

    @Query(
            value = "SELECT * FROM person WHERE username=?1",
            nativeQuery = true
    )
    Person findByUsername(String loginName);

    @Query(value = "select * from person where username = ?1 and password = ENCODE(?2,?3)",nativeQuery = true)
    Person findByUsernameAndPassword(String username, String password, String key);

    @Query(
            value = "select * from person where person_id=?",
            nativeQuery = true
    )
    Person findUserById(BigInteger id);

    @Query(
            value = "delete from person where person_id=?",
            nativeQuery = true
    )
    @Modifying
    void deleteByPerson_id(BigInteger id);

    @Query(
            value = "update person set rid=null where person_id=?",
            nativeQuery = true
    )
    @Modifying
    void deleteUserRole(BigInteger id);
    */
}